using System;
using System.Data;
using System.Data.SqlClient;
using <Application>.Models;

namespace <Application>.Data
{
    public class <TableU>Data
    {

        public static DataTable SelectAll()
        {
            SqlConnection connection = <Database>Data.GetConnection();
            string selectProcedure = "[<Owner>].[<TableU>SelectAll]";
            SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
            selectCommand.CommandType = CommandType.StoredProcedure;
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                if (reader.HasRows) {
                    dt.Load(reader); }
                reader.Close();
            }
            catch (SqlException)
            {
                return dt;
            }
            finally
            {
                connection.Close();
            }
            return dt;
        }

        public static DataTable Search(string sField, string sCondition, string sValue)
        {
            SqlConnection connection = <Database>Data.GetConnection();
            string selectProcedure = "[<Owner>].[<TableU>Search]";
            SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
            selectCommand.CommandType = CommandType.StoredProcedure;
    <If Loop>
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsRelated>
	       <And>
	       <IsMiscTag>
            if (sField == "<FieldC>") {
                selectCommand.Parameters.AddWithValue("@<RDisplayField><MiscTag>", sValue);
            } else {
                selectCommand.Parameters.AddWithValue("@<RDisplayField><MiscTag>", DBNull.Value); }
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsRelated>
	       <And>
	       <IsNotMiscTag>
            if (sField == "<FieldC>") {
                selectCommand.Parameters.AddWithValue("@<RDisplayField>", sValue);
            } else {
                selectCommand.Parameters.AddWithValue("@<RDisplayField>", DBNull.Value); }
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsNotRelated>
            if (sField == "<FieldC>") {
                selectCommand.Parameters.AddWithValue("@<FieldU>", sValue);
            } else {
                selectCommand.Parameters.AddWithValue("@<FieldU>", DBNull.Value); }
    </If Loop>
            selectCommand.Parameters.AddWithValue("@SearchCondition", sCondition);
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                if (reader.HasRows) {
                    dt.Load(reader); }
                reader.Close();
            }
            catch (SqlException)
            {
                return dt;
            }
            finally
            {
                connection.Close();
            }
            return dt;
        }

        public static <TableU> Select_Record(<TableU> <TableU>Para)
        {
            <TableU> <TableU> = new <TableU>();
            SqlConnection connection = <Database>Data.GetConnection();
            string selectProcedure = "[<Owner>].[<TableU>Select]";
            SqlCommand selectCommand = new SqlCommand(selectProcedure, connection);
            selectCommand.CommandType = CommandType.StoredProcedure;
        <If Loop>
           <IsPKey>
            selectCommand.Parameters.AddWithValue("@<FieldU>", <TableU>Para.<FieldU>);
        </If Loop>
        <If Loop>
           <IsNotPKey>
           <And>
           <IsIdentity>
            selectCommand.Parameters.AddWithValue("@<FieldU>", <TableU>Para.<FieldU>);
        </If Loop>
        <If Loop>
           <IsNotIdentityCount>
           <And>
           <IsNotPKeyCount>
   	   <And>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
            selectCommand.Parameters.AddWithValue("@<FieldU>", <TableU>Para.<FieldU>);
           <ExitLoop>
        </If Loop>
            try
            {
                connection.Open();
                SqlDataReader reader
                    = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.Read())
                {
                <If Loop>
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsFloat>
                    <TableU>.<FieldU> = reader["<Field>"] is DBNull ? null : (Decimal?)(Double?)reader["<Field>"];
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsReal>
                    <TableU>.<FieldU> = reader["<Field>"] is DBNull ? null : (Decimal?)(Single?)reader["<Field>"];
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsNotFloat>
                    <And>
                    <IsNotReal>
                    <TableU>.<FieldU> = reader["<Field>"] is DBNull ? null : (<VBType>?)reader["<Field>"];
                    <IsNull>
                    <And>
                    <IsNotNullable>
                    <TableU>.<FieldU> = reader["<Field>"] is DBNull ? null : reader["<Field>"].ToString();
                    <IsNotNull>
                    <TableU>.<FieldU> = <Convert>(reader["<Field>"]);
                </If Loop>
                }
                else
                {
                    <TableU> = null;
                }
                reader.Close();
            }
            catch (SqlException)
            {
                return <TableU>;
            }
            finally
            {
                connection.Close();
            }
            return <TableU>;
        }

        public static bool Add(<TableU> <TableU>)
        {
            SqlConnection connection = <Database>Data.GetConnection();
            string insertProcedure = "[<Owner>].[<TableU>Insert]";
            SqlCommand insertCommand = new SqlCommand(insertProcedure, connection);
            insertCommand.CommandType = CommandType.StoredProcedure;
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
            if (<TableU>.<FieldU>.HasValue == true) {
                insertCommand.Parameters.AddWithValue("@<FieldU>", <TableU>.<FieldU>);
            } else {
                insertCommand.Parameters.AddWithValue("@<FieldU>", DBNull.Value); }
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
            if (<TableU>.<FieldU> != null) {
                insertCommand.Parameters.AddWithValue("@<FieldU>", <TableU>.<FieldU>);
            } else {
                insertCommand.Parameters.AddWithValue("@<FieldU>", DBNull.Value); }
           <IsNotIdentity>
           <And>
           <IsNotNull>
            insertCommand.Parameters.AddWithValue("@<FieldU>", <TableU>.<FieldU>);
        </If Loop>
            insertCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
            insertCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
            try
            {
                connection.Open();
                insertCommand.ExecuteNonQuery();
                int count = System.Convert.ToInt32(insertCommand.Parameters["@ReturnValue"].Value);
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
        }

        public static bool Update(<TableU> old<TableU>, 
               <TableU> new<TableU>)
        {
            SqlConnection connection = <Database>Data.GetConnection();
            string updateProcedure = "[<Owner>].[<TableU>Update]";
            SqlCommand updateCommand = new SqlCommand(updateProcedure, connection);
            updateCommand.CommandType = CommandType.StoredProcedure;
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
            if (new<TableU>.<FieldU>.HasValue == true) {
                updateCommand.Parameters.AddWithValue("@New<FieldU>", new<TableU>.<FieldU>);
            } else {
                updateCommand.Parameters.AddWithValue("@New<FieldU>", DBNull.Value); }
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
            if (new<TableU>.<FieldU> != null) {
                updateCommand.Parameters.AddWithValue("@New<FieldU>", new<TableU>.<FieldU>);
            } else {
                updateCommand.Parameters.AddWithValue("@New<FieldU>", DBNull.Value); }
           <IsNotIdentity>
           <And>
           <IsNotNull>
            updateCommand.Parameters.AddWithValue("@New<FieldU>", new<TableU>.<FieldU>);
        </If Loop>
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
            updateCommand.Parameters.AddWithValue("@Old<FieldU>", old<TableU>.<FieldU>);
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
            if (old<TableU>.<FieldU>.HasValue == true) {
                updateCommand.Parameters.AddWithValue("@Old<FieldU>", old<TableU>.<FieldU>);
            } else {
                updateCommand.Parameters.AddWithValue("@Old<FieldU>", DBNull.Value); }
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
            if (old<TableU>.<FieldU> != null) {
                updateCommand.Parameters.AddWithValue("@Old<FieldU>", old<TableU>.<FieldU>);
            } else {
                updateCommand.Parameters.AddWithValue("@Old<FieldU>", DBNull.Value); }
        </If Loop>
            updateCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
            updateCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
            try
            {
                connection.Open();
                updateCommand.ExecuteNonQuery();
                int count = System.Convert.ToInt32(updateCommand.Parameters["@ReturnValue"].Value);
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
        }

        public static bool Delete(<TableU> <TableU>)
        {
            SqlConnection connection = <Database>Data.GetConnection();
            string deleteProcedure = "[<Owner>].[<TableU>Delete]";
            SqlCommand deleteCommand = new SqlCommand(deleteProcedure, connection);
            deleteCommand.CommandType = CommandType.StoredProcedure;
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
            deleteCommand.Parameters.AddWithValue("@Old<FieldU>", <TableU>.<FieldU>);
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
            if (<TableU>.<FieldU>.HasValue == true) {
                deleteCommand.Parameters.AddWithValue("@Old<FieldU>", <TableU>.<FieldU>);
            } else {
                deleteCommand.Parameters.AddWithValue("@Old<FieldU>", DBNull.Value); }
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
            if (<TableU>.<FieldU> != null) {
                deleteCommand.Parameters.AddWithValue("@Old<FieldU>", <TableU>.<FieldU>);
            } else {
                deleteCommand.Parameters.AddWithValue("@Old<FieldU>", DBNull.Value); }
        </If Loop>
            deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
            deleteCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
            try
            {
                connection.Open();
                deleteCommand.ExecuteNonQuery();
                int count = System.Convert.ToInt32(deleteCommand.Parameters["@ReturnValue"].Value);
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
